package com.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import com.dao.StudentDao;
import com.entity.Group;
import com.entity.Student;
import com.util.DBUtils;

public class StudentDaoImpl implements StudentDao {
	
	private Connection c;

	@Override
	public Integer insert(Student t) throws SQLException {
		c = DBUtils.getConnection();
		String sql = "insert into stu_tb(stu_name,stu_sex,stu_age,stu_major_id) values(?,?,?,?);";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, t.getStu_name());
		ps.setString(2, t.getStu_sex());
		ps.setInt(3, t.getStu_age());
		ps.setInt(4, t.getStu_major_id());
		int row = ps.executeUpdate();
		c.close();  
		return row;
	}

	@Override
	public Integer delete(Integer id) throws SQLException {
		c = DBUtils.getConnection();
		String sql = "delete from stu_tb where stu_id = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, id);
		int row = ps.executeUpdate();
		c.close();   
		return row;
	}

	@Override
	public Integer update(Student t) throws SQLException {
		c = DBUtils.getConnection();
		String sql = "update stu_tb set stu_major_id=? where stu_id = ? ";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, t.getStu_major_id());
		ps.setInt(2, t.getStu_id());
		int row = ps.executeUpdate();
		c.close();   
		return row;
	}

	@Override
	public Student selectById(Integer id) throws SQLException {
		c = DBUtils.getConnection();
		String sql = "SELECT stu_id,stu_name,stu_sex,stu_age,stu_major_id FROM stu_tb WHERE stu_id = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setInt(1, id);
		ResultSet rs = ps.executeQuery();
		Student s = null;
		if(rs.next()) {
			int stuId = rs.getInt(1);
			String stuName = rs.getString(2);
			String stuSex = rs.getString(3);
			int stuAge = rs.getInt(4);
			int majorId = rs.getInt(5);
			s = new Student(stuId, stuName, stuSex, stuAge, majorId);
		}
		return s;
	}

	@Override
	public List<Student> selectAll() throws SQLException {
		c = DBUtils.getConnection();
		String sql = "SELECT stu_id,stu_name,stu_sex,stu_age,stu_major_id FROM stu_tb";
		PreparedStatement ps = c.prepareStatement(sql);
		List<Student> students = new ArrayList<>();
		ResultSet rs = ps.executeQuery();
		while(rs.next()) {
			int stuId = rs.getInt(1);
			String stuName = rs.getString(2);
			String stuSex = rs.getString(3);
			int stuAge = rs.getInt(4);
			int majorId = rs.getInt(5);
			Student s = new Student(stuId, stuName, stuSex, stuAge, majorId);
			students.add(s);
		}
		return students.isEmpty() ? Collections.emptyList() : students ;
	}

	//排序按年龄升序输出
	@Override
	public List<Student> orderByAge() throws SQLException {
		c = DBUtils.getConnection();
		String sql = "SELECT stu_id,stu_name,stu_sex,stu_age,stu_major_id FROM stu_tb ORDER BY stu_age";
		PreparedStatement ps = c.prepareStatement(sql);
		List<Student> students = new ArrayList<>();
		ResultSet rs = ps.executeQuery();
		while(rs.next()) {
			int stuId = rs.getInt(1);
			String stuName = rs.getString(2);
			String stuSex = rs.getString(3);
			int stuAge = rs.getInt(4);
			int majorId = rs.getInt(5);
			Student s = new Student(stuId, stuName, stuSex, stuAge, majorId);
			students.add(s);
		}
		return students.isEmpty() ? Collections.emptyList() : students ;
	}

	//排序 按姓名
	@Override
	public List<Student> orderByName() throws SQLException {
		c = DBUtils.getConnection();
		String sql = "SELECT stu_id,stu_name,stu_sex,stu_age,stu_major_id FROM stu_tb ORDER BY stu_name";
		PreparedStatement ps = c.prepareStatement(sql);
		List<Student> students = new ArrayList<>();
		ResultSet rs = ps.executeQuery();
		while(rs.next()) {
			int stuId = rs.getInt(1);
			String stuName = rs.getString(2);
			String stuSex = rs.getString(3);
			int stuAge = rs.getInt(4);
			int majorId = rs.getInt(5);
			Student s = new Student(stuId, stuName, stuSex, stuAge, majorId);
			students.add(s);
		}
		return students.isEmpty() ? Collections.emptyList() : students ;
	}

	
	//按年龄分组 查询每个专业的人数
	@Override
	public List<Group> groupByAge() throws SQLException {
		c = DBUtils.getConnection();
		String sql = "SELECT stu_age,count(*) FROM stu_tb GROUP BY stu_age";
		PreparedStatement ps = c.prepareStatement(sql);
		List<Group> groups = new ArrayList<>();
		ResultSet rs = ps.executeQuery();
		while(rs.next()) {
			int age = rs.getInt(1);
			int count = rs.getInt(2);
			Group g = new Group(age,count);
			groups.add(g);
		}
		return groups.isEmpty() ? Collections.emptyList() : groups ;
	}

	//按专业分组查询人数
	@Override
	public List<Group> groupByMajor() throws SQLException {
		c = DBUtils.getConnection();
		String sql = "SELECT stu_major_id,count(*) FROM stu_tb GROUP BY stu_major_id;";
		PreparedStatement ps = c.prepareStatement(sql);
		List<Group> groups = new ArrayList<>();
		ResultSet rs = ps.executeQuery();
		while(rs.next()) {
			int majorId = rs.getInt(1);
			int count = rs.getInt(2);
			Group g = new Group(majorId,count);
			groups.add(g);
		}
		return groups.isEmpty() ? Collections.emptyList() : groups ;
	}


	
	
	
	
	

}
